package sf.database.mapper.handle;

import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.mapping.PropertyPath;
import org.springframework.data.repository.query.DefaultParameters;
import org.springframework.data.repository.query.Parameter;
import org.springframework.data.repository.query.Parameters;
import org.springframework.data.repository.query.ParametersParameterAccessor;
import org.springframework.data.repository.query.ParametersSource;
import org.springframework.data.repository.query.parser.Part;
import org.springframework.data.repository.query.parser.PartTree;
import sf.common.wrapper.Page;
import sf.database.dao.DBClient;
import sf.database.dialect.DBDialect;
import sf.database.jdbc.sql.SQLContext;
import sf.database.jdbc.sql.SQLParameter;
import sf.database.mapper.MapperInvoke;
import sf.database.meta.ColumnMapping;
import sf.database.meta.MetaHolder;
import sf.database.meta.TableMapping;
import sf.database.util.SQLUtils;
import sf.spring.util.CollectionUtils;
import sf.tools.ArrayUtils;
import sf.tools.StringUtils;

import java.lang.reflect.Array;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;


/**
 * 使用spring-data-commons实现,使用方法和spring-data-jpa一致
 * 自动生成sql
 */
public class MethodHandleAutoSQL implements MapperInvoke {
    protected static MethodHandleAutoSQL ms = new MethodHandleAutoSQL();

    public static MethodHandleAutoSQL getInstance() {
        return ms;
    }

    private MethodHandleAutoSQL() {

    }

    @Override
    public Object call(DBClient dbClient, Class entityClass, Method m, Object[] params) {

        Object ret = null;
        PartTree tree = new PartTree(m.getName(), entityClass);
        DBDialect dialect = dbClient.getDialect(false);
        if (tree.isDelete()) {
            SQLContext delete = createDelete(dialect, tree, entityClass, m, params);
            ret = parseUpdate(delete, m, dbClient);
        } else {
            List<SQLContext> selects = createQuery(dialect, tree, entityClass, m, params, true);
            ret = parseSelectList(tree, m, dbClient, selects);
        }
        return ret;
    }


    private SQLContext createDelete(DBDialect dialect, PartTree tree, Class entityClass, Method m, Object[] params) {
        TableMapping metadata = MetaHolder.getMeta(entityClass);
        Parameters parameters = new DefaultParameters(ParametersSource.of(m));
        ParametersParameterAccessor accessor = new ParametersParameterAccessor(
                parameters, params);
        List<List<Condition>> or = getConditions(dialect, tree, metadata, parameters, accessor);
        SQLContext context = new SQLContext();
        getWhere(context, or);
        if (tree.isDelete()) {
            StringBuilder sql = new StringBuilder();
            sql.append("delete from ").append(dialect.wrapKeyword(metadata.getTableName()));
            if (StringUtils.isNotBlank(context.getSql())) {
                sql.append(" where ").append(context.getSql());
            }
            context.setSql(sql.toString());
        }
        return context;
    }

    private void getWhere(SQLContext context, List<List<Condition>> condiss) {
        List<SQLParameter> parameters = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        StringBuilder sb1 = new StringBuilder();
        boolean f2 = false;
        List<String> a = new ArrayList<>();
        boolean useOr = condiss.size() > 1;
        for (List<Condition> list : condiss) {
            boolean f1 = false;
            for (Condition c : list) {
                sb1.append(f1 ? " and " : "").append(c.getSqlExpression());
                if (c.isHasParamter()) {
                    parameters.addAll(c.getParameters());
                }
                f1 = true;
            }
            a.add(sb1.toString());
            if (useOr) {
                sb.append(f2 ? " or " : "").append('(').append(sb1.toString()).append(')');
            } else {
                sb.append(sb1.toString());
            }
            f2 = true;
            sb1.delete(0, sb1.length());
        }
        context.setParas(parameters);
        context.setSql(sb.toString());

    }

    private List<List<Condition>> getConditions(DBDialect dialect, PartTree tree, TableMapping metadata, Parameters parameters, ParametersParameterAccessor accessor) {
        int index = 0;
        List<List<Condition>> or = new ArrayList<>();
        for (PartTree.OrPart node : tree) {
            List<Condition> and = new ArrayList<>();
            for (Part part : node) {
                PropertyPath path = part.getProperty();
                if (path.getOwningType().getType() != metadata.getThisType()) {
                    throw new IllegalArgumentException("PathType:"
                            + path.getOwningType().getType() + "  metadata:"
                            + metadata.getThisType());
                }
                String fieldName = path.getSegment();
                ColumnMapping field = metadata.findField(fieldName);
                int paramInfo = getBindParamIndex(index++, fieldName, parameters);
                Object obj = accessor.getBindableValue(paramInfo);
                if (field != null) {
                    and.add(build(dialect, part, field, obj));
                }
            }
            or.add(and);
        }
        return or;
    }

    private List<SQLContext> createQuery(DBDialect dialect, PartTree tree, Class entityClass, Method m, Object[] params, boolean withPageSort) {
        TableMapping metadata = MetaHolder.getMeta(entityClass);
        Parameters parameters = new DefaultParameters(ParametersSource.of(m));
        ParametersParameterAccessor accessor = new ParametersParameterAccessor(
                parameters, params);
        List<List<Condition>> or = getConditions(dialect, tree, metadata, parameters, accessor);
        SQLContext context = new SQLContext();
        getWhere(context, or);
        List<SQLContext> selects = new ArrayList<>();
        SQLContext select = null;
        SQLContext countSelect = null;
        boolean isPage = false;
        Pageable page = accessor.getPageable();
        if (tree.isCountProjection()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select count(*) from ").append(dialect.wrapKeyword(metadata.getTableName()));
            if (StringUtils.isNotBlank(context.getSql())) {
                sql.append(" where ").append(context.getSql());
            }
            context.setSql(sql.toString());
            select = context;
        } else if (tree.isDistinct()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select distinct * from ").append(dialect.wrapKeyword(metadata.getTableName()));
            if (StringUtils.isNotBlank(context.getSql())) {
                sql.append(" where ").append(context.getSql());
            }
            context.setSql(sql.toString());
            select = context;
        } else if (tree.isExistsProjection()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select count(*) from ").append(dialect.wrapKeyword(metadata.getTableName()));
            if (StringUtils.isNotBlank(context.getSql())) {
                sql.append(" where ").append(context.getSql());
            }
            context.setSql(sql.toString());
            select = context;
        } else {
            select = new SQLContext();
            StringBuilder sql = new StringBuilder();
            sql.append("select * from ").append(dialect.wrapKeyword(metadata.getTableName()));
            if (StringUtils.isNotBlank(context.getSql())) {
                sql.append(" where ").append(context.getSql());
            }
            select.setSql(sql.toString());
            select.setParas(context.getParas());
            if (page != Pageable.unpaged() && !tree.isCountProjection() && !tree.isExistsProjection()) {
                isPage = true;
                sql.delete(0, sql.length());
                sql.append("select count(*) from ").append(dialect.wrapKeyword(metadata.getTableName()));
                if (StringUtils.isNotBlank(context.getSql())) {
                    sql.append(" where ").append(context.getSql());
                }
                countSelect = new SQLContext();
                countSelect.setSql(sql.toString());
                countSelect.setParas(context.getParas());
            }
        }

        List<String> list = Collections.emptyList();
        if (withPageSort) {
            Sort sort = tree.getSort();
            if (sort == Sort.unsorted()) {
                sort = accessor.getSort();
            }
            if (sort == Sort.unsorted()) {
                sort = page.getSort();
            }
            if (sort != Sort.unsorted()) {
                list = setSortToSpec(dialect, sort, metadata);
            }
        }
        if (!list.isEmpty()) {
            String sql = select.getSql();
            sql = sql + " order by " + StringUtils.join(list, ",");
            select.setSql(sql);
        }
        selects.add(select);
        if (isPage) {
            select.putEnv("pageable", page);
            selects.add(countSelect);
        }

        return selects;
    }

    /**
     * 根据返回参数int 或者int[] 判断是否是批处理。如果都没有，根据第一参数判断
     * @param context
     * @param method
     * @param dbClient
     * @return
     */
    protected Object parseUpdate(SQLContext context, Method method, DBClient dbClient) {
        if (context != null) {
            return dbClient.execute(context);
        }
        return null;
    }

    protected Object parseSelectList(PartTree tree, Method method, DBClient dbClient, List<SQLContext> selects) {
        SQLContext select = null;
        SQLContext countSelect = null;
        if (!selects.isEmpty()) {
            select = selects.get(0);
            if (selects.size() > 1) {
                countSelect = selects.get(1);
            }
        }
        Class<?> returnType = method.getReturnType();
        Type retType = method.getGenericReturnType();
        Class[] paraClass = method.getParameterTypes();
        if (returnType == Page.class) {
            Class resultType = HandleHelp.getPageType(retType, Map.class);
            Pageable pageable = (Pageable) select.getEnv("pageable");
            long offset = pageable.getOffset();
            int limit = pageable.getPageSize();
            Long count = dbClient.selectOne(Long.class, countSelect);
            if (count == null) {
                count = 0L;
            }
            Page page = new Page(offset, limit, count);
            if (count > 0) {
                List list = dbClient.selectList(resultType, select, offset, limit);
                page.setList(list);
            }
            return page;
            //else否则就默认为mapper类型
        }
        if (Map.class.isAssignableFrom(returnType)) {
            //如果定义返回结果为Map，无论是否泛型，都认为返回一个Map
            return dbClient.selectOne(Map.class, select);
        } else if (List.class.isAssignableFrom(returnType)) {
            Class type = HandleHelp.getType(retType);
            if (type == null) {
                type = Map.class;
            }
            return dbClient.selectList(returnType, select);
        }


        //更改类型为Single
        if (tree.isExistsProjection()) {
            List l = dbClient.selectList(returnType, select, 0, 1);
            if (!l.isEmpty()) {
                return l.get(0);
            } else {
                return null;
            }
        } else {
            return dbClient.selectOne(returnType, select);
        }

    }

    private List<String> setSortToSpec(DBDialect dialect, Sort sort, TableMapping meta) {
        if (sort == null) {
            return Collections.emptyList();
        }
        List<String> list = new ArrayList<>();
        for (Sort.Order order : sort) {
            ColumnMapping column = meta.findField(order.getProperty());
            if (column != null) {
                if (order.isAscending()) {
                    list.add(dialect.wrapKeyword(column.getRawColumnName()) + " asc");
                } else {
                    list.add(dialect.wrapKeyword(column.getRawColumnName()) + " desc");
                }
            }
        }
        return list;
    }

    private int getBindParamIndex(int index, String fieldName, Parameters parameters) {
        for (int i = 0; i < parameters.getNumberOfParameters(); i++) {
            Parameter param = parameters.getParameter(i);
            if (param.getName().isPresent()) {
                if (fieldName.equals(param.getName().get())) {
                    return i;
                }
            } else {
                if (index == param.getIndex()) {
                    return i;
                }
            }
        }
        throw new NoSuchElementException("Can not found bind parameter '"
                + fieldName + "' in method ");
    }

    /**
     * Builds a JPA {@link } from the underlying {@link Part}.
     * @param part
     * @param cm
     * @param value
     * @return
     */
    public Condition build(DBDialect dialect, Part part, ColumnMapping cm, Object value) {

        PropertyPath property = part.getProperty();
        Part.Type type = part.getType();

        switch (type) {
            case BETWEEN: {
                Object[] objs = null;
                if (value.getClass().isArray()) {
                    objs = ArrayUtils.objectToArray(value);
                }

                String sqlExpression = dialect.wrapKeyword(dialect.wrapKeyword(cm.getRawColumnName())) + " between " + SQLUtils.PLACEHOLDER + " and " + SQLUtils.PLACEHOLDER;
                if (objs != null && objs.length > 1) {
                    SQLParameter p1 = new SQLParameter(objs[0], cm);
                    SQLParameter p2 = new SQLParameter(objs[1], cm);
                    Condition condi = new Condition();
                    condi.setSqlExpression(sqlExpression);
                    condi.setParameters(Arrays.asList(p1, p2));
                    condi.setHasParamter(true);
                    return condi;
                }
            }
            case AFTER:
            case GREATER_THAN: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " > " + SQLUtils.PLACEHOLDER + " ";
                SQLParameter p1 = new SQLParameter(value, cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case GREATER_THAN_EQUAL: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " >= " + SQLUtils.PLACEHOLDER + " ";
                SQLParameter p1 = new SQLParameter(value, cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case BEFORE:
            case LESS_THAN: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " < " + SQLUtils.PLACEHOLDER + " ";
                SQLParameter p1 = new SQLParameter(value, cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case LESS_THAN_EQUAL: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " <= " + SQLUtils.PLACEHOLDER + " ";
                SQLParameter p1 = new SQLParameter(value, cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case IS_NULL: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " is null ";
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                return condi;
            }
            case IS_NOT_NULL: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " is not null ";
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                return condi;
            }
            case NOT_IN: {
                StringBuilder sb = new StringBuilder();
                sb.append(dialect.wrapKeyword(cm.getRawColumnName())).append(" not in (");
                List<SQLParameter> list = new ArrayList<>();
                boolean empty = false;
                if (Collection.class.isAssignableFrom(value.getClass())) {
                    boolean f = false;
                    Collection<?> values = (Collection<?>) value;
                    empty = CollectionUtils.isEmpty(values);
                    for (Object val : values) {
                        sb.append(f ? "," : "").append(SQLUtils.PLACEHOLDER);
                        SQLParameter p1 = new SQLParameter(val, cm);
                        list.add(p1);
                        f = true;
                    }
                } else if (value.getClass().isArray()) {
                    int length = Array.getLength(value);
                    empty = length == 0;
                    for (int i = 0; i < length; i++) {
                        Object val = Array.get(value, i);
                        sb.append(i > 0 ? "," : "").append(SQLUtils.PLACEHOLDER);
                        SQLParameter p1 = new SQLParameter(val, cm);
                        list.add(p1);
                    }
                } else {
                    sb.append(SQLUtils.PLACEHOLDER);
                    SQLParameter p1 = new SQLParameter(value, cm);
                    list.add(p1);
                }
                sb.append(')');
                Condition condi = new Condition();
                if (!empty) {
                    condi.setSqlExpression(sb.toString());
                    condi.setParameters(list);
                    condi.setHasParamter(true);
                } else {
                    condi.setSqlExpression("1=1");//not in永真
                }
                return condi;
            }
            case IN: {
                StringBuilder sb = new StringBuilder();
                sb.append(dialect.wrapKeyword(cm.getRawColumnName())).append(" in (");
                List<SQLParameter> list = new ArrayList<>();
                boolean empty = false;
                if (Collection.class.isAssignableFrom(value.getClass())) {
                    boolean f = false;
                    Collection<?> values = (Collection<?>) value;
                    empty = CollectionUtils.isEmpty(values);
                    for (Object val : values) {
                        sb.append(f ? "," : "").append(SQLUtils.PLACEHOLDER);
                        SQLParameter p1 = new SQLParameter(val, cm);
                        list.add(p1);
                        f = true;
                    }
                } else if (value.getClass().isArray()) {
                    int length = Array.getLength(value);
                    empty = length == 0;
                    for (int i = 0; i < length; i++) {
                        Object val = Array.get(value, i);
                        sb.append(i > 0 ? "," : "").append(SQLUtils.PLACEHOLDER);
                        SQLParameter p1 = new SQLParameter(val, cm);
                        list.add(p1);
                    }
                } else {
                    sb.append(SQLUtils.PLACEHOLDER);
                    SQLParameter p1 = new SQLParameter(value, cm);
                    list.add(p1);
                }
                sb.append(')');
                Condition condi = new Condition();
                if (!empty) {
                    condi.setSqlExpression(sb.toString());
                    condi.setParameters(list);
                    condi.setHasParamter(true);
                } else {
                    condi.setSqlExpression("1=0");//not in永假
                }
                return condi;
            }
            case STARTING_WITH: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " like " + SQLUtils.PLACEHOLDER + " escape '/' ";
                SQLParameter p1 = new SQLParameter("%" + value, cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case ENDING_WITH: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " like " + SQLUtils.PLACEHOLDER + " escape '/' ";
                SQLParameter p1 = new SQLParameter(value + "%", cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case CONTAINING:
            case LIKE: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " like " + SQLUtils.PLACEHOLDER + " escape '/' ";
                SQLParameter p1 = new SQLParameter("%" + value + "%", cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case NOT_CONTAINING:
            case NOT_LIKE: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " not like " + SQLUtils.PLACEHOLDER + " escape '/' ";
                SQLParameter p1 = new SQLParameter("%" + value + "%", cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case TRUE:
            case FALSE: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + (type.equals(Part.Type.TRUE) ? " is true " : "is false ");
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                return condi;
            }
            case SIMPLE_PROPERTY: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " = " + SQLUtils.PLACEHOLDER + " ";
                SQLParameter p1 = new SQLParameter(value, cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case NEGATING_SIMPLE_PROPERTY: {
                String sqlExpression = dialect.wrapKeyword(cm.getRawColumnName()) + " <> " + SQLUtils.PLACEHOLDER + " ";
                SQLParameter p1 = new SQLParameter(value, cm);
                Condition condi = new Condition();
                condi.setSqlExpression(sqlExpression);
                condi.setParameters(Collections.singletonList(p1));
                condi.setHasParamter(true);
                return condi;
            }
            case IS_EMPTY:
            case IS_NOT_EMPTY:
                if (!property.getLeafProperty().isCollection()) {
                    throw new IllegalArgumentException("IsEmpty / IsNotEmpty can only be used on collection properties!");
                }
//                return type.equals(IS_NOT_EMPTY) ? null : null;

            default:
                throw new IllegalArgumentException("Unsupported keyword " + type);
        }
    }


}
